Release 10.1A: OpenEdge Data Management:
SQL Development

Outer joins

An outer join between two tables returns more information than a corresponding inner join. An outer join returns a result table that contains all the rows from one of the tables even if there is no row in the other table that satisfies the join condition.

In a left outer join, the information from the table on the left is preserved: the result table contains all rows from the left table even if some rows do not have matching rows in the right table. Where there are no matching rows in the right table, SQL generates NULL values.

In a right outer join, the information from the table on the right is preserved. The result table contains all rows from the right table even if some rows do not have matching rows in the left table. Where there are no matching rows in the left table, SQL generates NULL values.

SQL uses two forms of syntax to support outer joins:

In the WHERE clause of a query expression, specify the outer join operator ( + ) after the column name of the table for which rows will not be preserved in the result table. Both sides of an outer join search condition in a WHERE clause must be simple column references. This syntax allows both left and right outer joins.
For left outer joins only, in the FROM clause, specify the LEFT OUTER JOIN clause between two table names, followed by a search condition. The search condition can contain only the join condition between the specified tables.

Full (two-sided) outer joins are not supported. Right outer joins are only supported using the outer join operator in the WHERE clause. The keywords RIGHT OUTER JOIN are not supported currently.

This is the syntax for a from_clause_outer_join:

Syntax

FROM table_ref LEFT OUTER JOIN table_ref ON search_condition

This is the syntax for a where_clause_outer_join:

Syntax

WHERE [ table_name.] column (+) = [ table_name.]column | WHERE [ table_name.]column = [ table_name.]column (+)

The following example shows a left outer join:

SELECT Customer.Custnum, Customer.Name, Order.Ordernum, Orders.Orderdate FROM Customer, Order WHERE Customer.CustNum = Order.CustNum (+) ;

The query requests information about all the customers and their orders. Even if there is not a corresponding row in the Order table for each row in the Customer table, NULL values are displayed for the Order.Ordernum and Order.Orderdate columns.

The query produces the results table shown in Example 6–12.

Example 6–12: Left outer join

[1] CustNum: 1 [1] Name: Lift Tours [1] Ordernum: 6 [1] OrderDate: 2003-02-11 [2] CustNum: 1 [2] Name: Lift Tours [2] Ordernum: 36 [2] OrderDate: 2003-09-27 [3] CustNum: 1 [3] Name: Lift Tours [3] Ordernum: 79 [3] OrderDate: 2003-11-21

Example 6–13 illustrates outer join syntax.

Example 6–13: Two different outer joins producing same result

SELECT * FROM T1; -- Contents of T1 C1 C2 -- -- 10 15 20 25 SELECT * FROM T2; -- Contents of T2 C3 C4 -- -- 10 BB 15 DD -- Left outer join SELECT * FROM T1 LEFT OUTER JOIN T2 ON T1.C1 = T2.C3; C1 C2 C3 C4 -- -- -- -- 10 15 10 BB 20 25 -- Left outer join: different formulation, same results SELECT * FROM T1, T2 WHERE T1.C1 = T2.C3 (+); C1 C2 C3 C4 -- -- -- -- 10 15 10 BB 20 25